package nccloud.message.listener;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.alibaba.fastjson.JSONObject;

import nc.bs.businessevent.BusinessEvent;
import nc.bs.businessevent.IBusinessEvent;
import nc.bs.businessevent.IBusinessListener;
import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.framework.common.NCLocator;
import nc.bs.framework.server.ISecurityTokenCallback;
import nc.bs.uap.lock.PKLock;
import nc.itf.hr.frame.IPersistenceRetrieve;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.jdbc.framework.processor.MapProcessor;
import nc.message.msgcenter.event.MessageStatesChangeContext;
import nc.vo.hi.psndoc.PsndocVO;
import nc.vo.pf.worknote.WorkNoteVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.sm.UserVO;
import nccloud.bs.message.itf.IMessageSyncService;
import nccloud.message.vo.NCCApproveMessageVO;
import nccloud.message.vo.NCCMessageVO;
import nccloud.riart.pub.utils.HttpUtil;
import nccloud.utils.mobile_riart.MessageLogger;
import nccloud.utils.riart.MessageEventTypeEnum;
import nccloud.utils.riart.MsgSyncUtils;

/**
 * 20220520 wzx 插件
 *
 * 待办任务 pub_workflownote userobject 默认：空 成功：已推送 失败 ：推送失败 待办变已办 pub_workflownote
 * userobject 默认：空 成功：已同步 失败 ：同步失败 删除待办 sm_msg_user dr 默认：0 成功：1 失败：2
 *
 * @author wzx
 *
 */

public class SynToDoMessageListener implements IBusinessListener {

	private BaseDAO baseDAO = null;
	// 调用NCC系统单点登录系统的基础URL
	private static final String BASE_APPROVE_URL = "/nccloud/sso/visit.action?";
	private static final String BASE_APPROVE_URL_MOB = "/nccloud/resources/mobile_plan/app/baseData/main/#/msgcenter/AllMsg?isApprove=N";
	// 线程池
	private ExecutorService executor = Executors.newCachedThreadPool();

	@Override
	public void doAction(IBusinessEvent source) throws BusinessException {
		// 后台任务转为业务插件 逻辑不变
		if (source instanceof BusinessEvent) {
			// wzx 20220520 不根据消息类型、主键判断是否发送，将定时任务逻辑挪到这里，直接触发，每次更新数据库直接提交
			try {
				// 处理待办 已办 收回待办
				MessageLogger.info("#CJM#进入待办监听事件");
				nc.bs.logging.Logger.error("#CJM#进入待办监听事件");
				NCLocator.getInstance().lookup(ISecurityTokenCallback.class).token("NCSystem".getBytes(),"pfxx".getBytes());
				String dataSource = MsgSyncUtils.queryGroupSysParam("BD900");
				InvocationInfoProxy.getInstance().setUserDataSource(dataSource);
				MessageLogger.info("第三方请求NCC调用的方法【OpenMessage】模拟登陆完成：数据源="+dataSource);
				Object sourceObj = ((BusinessEvent) source).getObject();
				if (sourceObj instanceof MessageStatesChangeContext) {
					MessageStatesChangeContext content = (MessageStatesChangeContext) sourceObj;
					String type = content.getMsgType();
					String eventStates = content.getEventStates();
					String[] handlepks = content.getHandlepks();
					Object messageObj = content.getMessage();
					if ("add".equals(eventStates)) {
						// 新增暂时走定时任务
						MessageLogger.info("#CJM#进入待办监听add事件");
						nc.bs.logging.Logger.error("#CJM#进入待办监听add事件");
						if (null != messageObj && messageObj instanceof ArrayList) {
							for (Object singleMsgObj : (ArrayList) messageObj) {
								if (singleMsgObj instanceof NCCApproveMessageVO){
									// 处理待办消息
									NCCApproveMessageVO approveMessageVO = (NCCApproveMessageVO) singleMsgObj;
									approveMessageVO.getPk_message();
									approveMessageVO.getPk_detail();
									approveMessageVO.getBillid();
									approveMessageVO.getBillno();
									sendToDoMessageRunable(approveMessageVO);
								}
								// else if(singleMsgObj instanceof NCCNoticeMessageVO) {
								// //处理通知消息
								// NCCNoticeMessageVO noticeMessageVO = (NCCNoticeMessageVO)singleMsgObj;
								//
								// }else if(singleMsgObj instanceof NCCPrealertMessageVO) {
								// //处理预警消息
								// NCCPrealertMessageVO prealertMessageVO = (NCCPrealertMessageVO)singleMsgObj;
								// }
							}
						}
					} else {
						// 除了新增以外的 都走更新操作 根据消息的主键获取数据 判断走待办变已办还是走删除待办
						// 20220627 驳回、收回、审批完成会走update，需要根据查询到的数据判断走哪个接口
						String pk_detail = new String();
						String pk_user = new String();
						if (null != messageObj) {
							if (messageObj instanceof List) {
								ArrayList messageList = (ArrayList) messageObj;
								for (Object tempMessage : messageList) {
									if (tempMessage instanceof JSONObject) {
										JSONObject messageItemJson = (JSONObject) tempMessage;
										pk_detail = messageItemJson.get("pk_detail") == null ? ""
												: (String) messageItemJson.get("pk_detail");
										ArrayList receivers = messageItemJson.get("receivers") == null ? new ArrayList()
												: (ArrayList) messageItemJson.get("receivers");
										for (Object userObj : receivers) {
											if (userObj instanceof JSONObject) {
												JSONObject userJson = (JSONObject) userObj;
												pk_user = userJson.get("pk_user") == null ? ""
														: (String) userJson.get("pk_user");
												checkStatusByPk_detail(pk_detail, pk_user);
											}
										}
									}
								}

							} else if (messageObj instanceof HashMap) {
								HashMap messageHashMap = (HashMap) messageObj;
								pk_detail = null == messageHashMap.get("pk_detail") ? ""
										: (String) messageHashMap.get("pk_detail");
								pk_user = null == messageHashMap.get("pk_user") ? ""
										: (String) messageHashMap.get("pk_user");
								// 查询消息列表，根据当前状态确定是否要把待办变为已办
								checkStatusByPk_detail(pk_detail, pk_user);
							}
						}
					}
				}
			} catch (Exception e) {
				MessageLogger.info("OA通知消息发送异常【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】.......");
				nc.bs.logging.Logger.error("OA通知消息发送异常");
			}
		}
	}

	/**
	 * 发送待办消息 加同步锁 不允许同时执行，防止一条消息发多次
	 */
	public void sendToDoMessageRunable(NCCApproveMessageVO approveMessageVO) throws Exception {
		executor.submit(new Runnable() {
			@Override
			public void run() {
				try {
					// 休息两秒保证消息可以保存到数据库中
					Thread.sleep(4000);
					// 发送待办消息
					MessageLogger.error("#CJM#开始发送待办消息");
					nc.bs.logging.Logger.error("#CJM#开始发送待办消息");
//					sendTodoMessage(approveMessageVO);
					Collection<NCCMessageVO> tempMsgVos = getBaseDAO().retrieveByClause(NCCMessageVO.class, 
							"pk_message in ('"+approveMessageVO.getPk_message()+"')");
					NCLocator.getInstance().lookup(IMessageSyncService.class).setMessageCache(tempMsgVos.toArray(new NCCMessageVO[0]), MessageEventTypeEnum.ADD);
				} catch (Exception e) {
					MessageLogger.info("待办消息线程异常【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】........");
					throw new RuntimeException("待办消息线程异常！");
				}
			}
		});

	}

	/**
	 *  根据信息查询消息isdelete 以及isread标识，判断调用删除接口还是待办完成接口
	 * @param pk_detail
	 * @param pk_user
	 */
	public void checkStatusByPk_detail(String pk_detail, String pk_user) {
		executor.submit(new Runnable() {
			@SuppressWarnings("unchecked")
			@Override
			public void run() {
				try {
//					String dataSource = MsgSyncUtils.queryGroupSysParam("BD900");
					InvocationInfoProxy.getInstance().setUserDataSource("design");
					// 休息三秒保证消息可以保存到数据库中
					Thread.sleep(4000);
					IUAPQueryBS queryService = NCLocator.getInstance().lookup(IUAPQueryBS.class);
					//20210328 补充完成待办的条
					String queryMessageStateSql = "select sm_msg_user.isdelete," +
							"       sm_msg_user.isread, " +
							"       sm_msg_user.pk_message, " +
							"       pub_workflownote.billno," +
							"       pub_workflownote.approveresult, " +
							"       pub_workflownote.approvestatus, " +
							"       pub_workflownote.userobject " +
							"  from sm_msg_user" +
							"  left join sm_msg_approve" +
							"    on sm_msg_user.pk_message = sm_msg_approve.pk_message" +
							"  left join pub_workflownote" +
							"    on pub_workflownote.pk_checkflow = sm_msg_approve.pk_detail" +
							" where sm_msg_approve.pk_detail = '"+pk_detail+"'" +
							"   and sm_msg_user.pk_user = '"+pk_user+"'" +
							"   and sm_msg_user.destination = 'inbox'" +
							"   and sm_msg_user.message_type = 'approve';";
					BaseDAO dao = getBaseDAO();
					Map<String, Object> resultMap = (Map<String, Object>) dao.executeQuery(queryMessageStateSql, new MapProcessor());
//					Map<String, Object> resultMap = (Map<String, Object>) queryService.executeQuery(queryMessageStateSql, new MapProcessor());

					if (null != resultMap && resultMap.size() > 0) {
						String isdelete = resultMap.get("isdelete")==null?"":(String)resultMap.get("isdelete");
						String isread =  resultMap.get("isread")==null?"":(String)resultMap.get("isread");
						String pk_message = resultMap.get("pk_message")==null?"":(String)resultMap.get("pk_message");
						String userobject =  resultMap.get("userobject")==null?"":(String)resultMap.get("userobject");
						int approvestatus = resultMap.get("approvestatus")==null?0:(Integer)resultMap.get("approvestatus");
						if ("Y".equals(isdelete)) {
							// 处理删除
//							withdrawTodoMessage(pk_message);
							Collection<NCCMessageVO> tempMsgVos = getBaseDAO().retrieveByClause(NCCMessageVO.class,
									" pk_message IN ('" + pk_message+ "')");
							NCLocator.getInstance().lookup(IMessageSyncService.class)
							.setMessageCache(tempMsgVos.toArray(new NCCMessageVO[0]), MessageEventTypeEnum.DELETE);
						} else if ("Y".equals(isread) && 0!= approvestatus) {
							// 已发送的待办变已办
//							sendTodoDoneMessage(pk_detail, pk_user);
							Collection<NCCMessageVO> tempMsgVos = getBaseDAO().retrieveByClause(NCCMessageVO.class,
									" pk_message IN ('" + pk_message+ "')");
							NCLocator.getInstance().lookup(IMessageSyncService.class)
							.setMessageCache(tempMsgVos.toArray(new NCCMessageVO[0]), MessageEventTypeEnum.UPDATE);
						}
					}
				} catch (Exception e) {
					MessageLogger.info("待办变已办消息线程异常！【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】........");
					throw new RuntimeException("待办变已办消息线程异常！");
				}
			}
		});
	}

	/**
	 *  通知消息 加同步锁 不允许同时执行，防止一条消息发多次
	 * @throws Exception
	 */
	public synchronized void sendNoticeMessageRunable() throws Exception {
		executor.submit(new Runnable() {
			@Override
			public void run() {
				try {
					// 休息两秒保证消息可以保存到数据库中
					// Thread.sleep(4000);
					// 发送通知消息
					// sendNoticeMessage();
				} catch (Exception e) {
					MessageLogger.info("发送通知消息线程异常！【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】........");
					throw new RuntimeException("发送通知消息线程异常！");
				}
			}
		});
	}

	/**
	 *  预警消息 加同步锁 不允许同时执行，防止一条消息发多次
	 * @throws Exception
	 */
	public synchronized void sendPrealertMessageRunable() throws Exception {
		executor.submit(new Runnable() {
			@Override
			public void run() {
				try {
					// 休息两秒保证消息可以保存到数据库中
					// Thread.sleep(4000);
					// 发送预警消息
					// sendPrealertMessage();
				} catch (Exception e) {
					MessageLogger.info("发送预警消息线程异常！【nccloud.message.listener.SynToDoMessageListener】，，错误描述【"+e.getMessage()+"】........");
					throw new RuntimeException("发送预警消息线程异常！");
				}
			}
		});
	}

	/**
	 * 发送待办消息
	 */
	private void sendTodoMessage(NCCApproveMessageVO approveMessageVO) {
		try {
			String api = "/rest/ofs/ReceiveRequestInfoByJson";
			MessageLogger.error("#CJM#开始查询NC待办消息");
			nc.bs.logging.Logger.error("#CJM#开始查询NC待办消息");
			List<WorkNoteVO> unapproveBillList = this.getUnapproveBill(approveMessageVO);
			if(unapproveBillList!=null && unapproveBillList.size()>0) {
				for (WorkNoteVO workNoteVO : unapproveBillList) {
					boolean sendUamPost = true;
					String result = "";
					// 转为请求接口的数据格式
					MessageLogger.error("#CJM#开始生成统一认证消息接口数据");
					nc.bs.logging.Logger.error("#CJM#开始生成统一认证消息接口数据");
					Map<String,String> mapData = this.unapproveBillsToOAToDO(workNoteVO);
					// 请求接口获取返回结果
					if(mapData.get("oa")!=null) {

						//add by chenjma 增加并发枷锁控制，防止即时和定时任务重复发送
						boolean flag = PKLock.getInstance().addDynamicLock(workNoteVO.getPk_checkflow());
						if(!flag) {
							MessageLogger.error("【SynToDoMessageListener】待办消息主键加锁失败！并发异常。");
							throw new BusinessException("【SynToDoMessageListener】待办消息主键加锁失败！并发异常。");
						}
						
						//add by chenjma 20221202 增加数据库并发锁
						String lockSQL = "select PK_CHECKFLOW from pub_workflownote where PK_CHECKFLOW='" + workNoteVO.getPk_checkflow() + "' for update nowait";
//						dao.executeUpdate(lockSQL);
						getBaseDAO().executeQuery(lockSQL, new MapProcessor());
						MessageLogger.error("发送待办消息PendingTrial ----- oamapData: "+mapData.size());
						nc.bs.logging.Logger.error("PendingTrial ----- oamapData: "+mapData.size());
						result = HttpUtil.sendUamPost(api, mapData.get("oa"), "POST", "tongyirenzhengDaiBan_ADD_"+workNoteVO.getPk_checkflow());
						sendUamPost = HttpUtil.analysisResult(result);
						nc.bs.logging.Logger.error("请求OA发送接口返回成功");
						MessageLogger.error("请求OA发送接口返回成功");
					}
					// 回写自定义档案 记录信息
					// this.addDefdoc(workNoteVO,"HRTYRN_01",mapData,result);
//					IDataSynLog lookup = NCLocator.getInstance().lookup(IDataSynLog.class);
//					lookup.addDefdocPendingLog_RequiresNew(workNoteVO,"HRTYRN_01",mapData,result);
					// 同步更新消息状态
					if(sendUamPost) {
						MessageLogger.error("PendingTrial ----- 同步更新消息状态开始 ");
						nc.bs.logging.Logger.error("PendingTrial ----- 同步更新消息状态开始 ");
						this.synNCCBills(workNoteVO);
						MessageLogger.error("PendingTrial ----- 同步更新消息状态结束 ");
						nc.bs.logging.Logger.error("PendingTrial ----- 同步更新消息状态结束 ");
					}
				}
			}
		} catch (Exception e) {
			MessageLogger.info("待办消息发送异常【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】........");
			nc.bs.logging.Logger.error("待办消息发送异常");
			nc.bs.logging.Logger.error(e);
		}
	}

	/**
	 * 待办变已办消息
	 */
	private void sendTodoDoneMessage(String pk_detail, String pk_user) {
		try {
			String api = "/rest/ofs/ProcessDoneRequestByJson";
			// 查询未同步的异常审批工作
			List<WorkNoteVO> unSynBills = this.getUnSynApprovedBill(pk_detail, pk_user);
			if(unSynBills !=null && unSynBills.size()>0) {
				for (WorkNoteVO workNoteVO : unSynBills) {
					String result = "";
					boolean sendUamPost = false;
					// 转为请求OA接口的数据格式
					String postData = this.synApprovedBillsToOA(workNoteVO);
					nc.bs.logging.Logger.error("CompensationPaidBgPlugin ----- 开始请求OA接口");
					// 请求接口获取返回结果
					if(null!=postData) {
						result = HttpUtil.sendUamPost(api, postData, "POST", "tongyirenzhengDaiBan_UPDATE_"+workNoteVO.getPk_checkflow());
						nc.bs.logging.Logger.error("请求OA更新已办接口返回成功：" + result);
						// 同步更新消息状态
						sendUamPost = HttpUtil.analysisResult(result);
						if(sendUamPost) {
							this.synNCCBillsDone(workNoteVO);
						}
						// 回写自定义档案 记录信息
						// this.addDefdocUpdate(workNoteVO,"HRTYRN_02",postData,result);
//						IDataSynLog lookup = NCLocator.getInstance().lookup(IDataSynLog.class);
//						lookup.addDefdocCompensationLog_RequiresNew(workNoteVO, "HRTYRN_02", postData, result);
					}
				}
			}
		} catch (Exception e) {
			MessageLogger.info("已办消息发送异常【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】.......");
			nc.bs.logging.Logger.error("已办消息发送异常");
			nc.bs.logging.Logger.error(e);
		}
	}

	/**
	 * 收回待办消息
	 */
	private void withdrawTodoMessage(String pk_message) {
		try {
			nc.bs.logging.Logger.error("WithdrawPending ----- 开始执行删除待办");
			String api = "/rest/ofs/deleteUserRequestInfoByJson";
			List<Map<String, String>> mapList = this.getWithDrawBill(pk_message);
			if(mapList!=null && mapList.size()>0) {
				for (Map<String, String> map : mapList) {
					String message_service = map.get("message_service");
					boolean sendUamPost = false;
					String result = "";
					if(message_service!=null && !"~".equals(message_service)) {
						// 请求OA接口获取返回结果
						String postData = queryMapToContext(map);
						result = HttpUtil.sendUamPost(api+postData, "", "DELETE", "tongyirenzhengDaiBan_DELETE_"+map.get("pk_detail"));
						sendUamPost = HttpUtil.analysisResult(result);
						nc.bs.logging.Logger.error("请求OA撤回接口返回成功");
						// this.addDefdocDel(postData,"HRTYRN_03",result,map.get("billno"),map.get("pk_detail"));
//						IDataSynLog lookup = NCLocator.getInstance().lookup(IDataSynLog.class);
//						lookup.addDefdocWithdrawLog_RequiresNew(postData, "HRTYRN_03", result, map.get("billno"),map.get("pk_detail"));
					}
					// 同步更新消息状态
					if(sendUamPost) {
						this.synNCCBills(map);
					}

				}
			}
		} catch (Exception e) {
			MessageLogger.info("收回待办消息异常【nccloud.message.listener.SynToDoMessageListener】，错误描述【"+e.getMessage()+"】........");
			nc.bs.logging.Logger.error("收回待办消息异常");
			nc.bs.logging.Logger.error("WithdrawPending:", e);
			e.printStackTrace();
		}

	}

	/**
	 * 获取所有待办消息
	 *
	 * @return
	 * @throws BusinessException
	 */
	@SuppressWarnings("unchecked")
	private List<WorkNoteVO> getUnapproveBill(NCCApproveMessageVO approveMessageVO) throws BusinessException {
		// 需要增加时间条件 一天以前的数据不发送
		IUAPQueryBS queryService = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		String pk_detail = approveMessageVO.getPk_detail();
		String queryWorkNoteSql = "select * from pub_workflownote where actiontype = 'Z' and approvestatus = 0 and pk_checkflow = '"
				+ pk_detail + "' and userObject is null";
		List<WorkNoteVO> unapproveBillList = (List<WorkNoteVO>) queryService.executeQuery(queryWorkNoteSql,
				new BeanListProcessor(WorkNoteVO.class));
		return unapproveBillList;
	}

	/**
	 * 查询审批完成的消息
	 *
	 * @return
	 * @throws BusinessException
	 */
	@SuppressWarnings("unchecked")
	private List<WorkNoteVO> getUnSynApprovedBill(String pk_detail, String pk_user) throws BusinessException {
		IUAPQueryBS queryService = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		// 20210328 补充完成待办的条
		String queryWorkNoteSql = "select * from pub_workflownote where PK_CHECKFLOW = '" + pk_detail
				//				+ "' and checkman = '" + pk_user + "' and userObject = '已推送' ";
				+ "' and checkman = '" + pk_user + "'";
		List<WorkNoteVO> unapproveBillList = (List<WorkNoteVO>) queryService.executeQuery(queryWorkNoteSql,
				new BeanListProcessor(WorkNoteVO.class));
		return unapproveBillList;
	}

	/**
	 * 获取所有需要删除的待办
	 *
	 * @return
	 * @throws BusinessException
	 */
	@SuppressWarnings("unchecked")
	private List<Map<String, String>> getWithDrawBill(String pk_message) throws BusinessException {
		List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
		// 需要增加时间条件 一天以前的数据不发送
		UFDateTime dt = new UFDateTime(System.currentTimeMillis() - 60 * 24 * 60 * 1000);
		//		String querySql = "select a.pk_message_user, a.pk_message, a.pk_user,b.message_service,b.pk_detail from sm_msg_user a "
		//				+ " left join sm_msg_approve b on a.pk_message=b.pk_message where "
		//				+ " a.destination = 'inbox' and a.pk_message ='" + pk_message
		//				+ "' and a.isdelete = 'Y' and nvl(a.dr,0) = 0 and a.message_type = 'approve' ";
		String querySql = "select a.pk_message_user, a.pk_message, a.pk_user,b.message_service,b.pk_detail,b.billno from sm_msg_user a "
				+ " left join sm_msg_approve b on a.pk_message=b.pk_message where "
				+ " a.destination = 'inbox' and a.isdelete = 'Y' and nvl(a.dr,0) = 0 and a.message_type = 'approve' ";
		nc.bs.logging.Logger.error("收回待办sql" + querySql);
		Object coll = getBaseDAO().executeQuery(querySql, new MapListProcessor());
		if (null != coll) {
			mapList = (ArrayList<Map<String, String>>) coll;
		}
		return mapList;
	}

	/**
	 * 预警消息
	 * @return
	 * @throws DAOException
	 */
	private List<Map<String, String>> getPrealertMessage() throws DAOException {
		// 需要增加时间条件 一天以前的数据不发送
		UFDateTime dt = new UFDateTime(System.currentTimeMillis() - 60 * 24 * 60 * 1000);
		String querySql = "select sm_msg_user.pk_message_user," + "       sm_msg_user.pk_user,"
				+ "       sm_msg_user.message_type," + "       bd_psndoc.def10 psndoccode,"
				+ "       sm_msg_prealert.sendtime," + "       sm_msg_prealert.subject" + "  from sm_msg_user"
				+ "  left join sm_msg_prealert" + "    on sm_msg_user.pk_message = sm_msg_prealert.pk_message"
				+ "  left join sm_user" + "    on sm_msg_user.pk_user = sm_user.cuserid" + "  left join bd_psndoc"
				+ "    on sm_user.pk_psndoc = bd_psndoc.pk_psndoc" + " where sm_msg_user.destination = 'inbox'"
				+ "   and sm_msg_user.message_type = 'prealert'" + "   and isdelete = 'N'" + "   and isread = 'N'"
				+ "   and nvl(sm_msg_user.dr,0) = 0 and sm_msg_user.ts > '" + dt + "';";
		List<Map<String, String>> peralertList = (List<Map<String, String>>) getBaseDAO().executeQuery(querySql,
				new MapListProcessor());

		return peralertList;
	}

	/**
	 * 通知消息
	 * @return
	 * @throws DAOException
	 */
	private List<Map<String, String>> getNoticeMessage() throws DAOException {
		// 需要增加时间条件 一天以前的数据不发送
		UFDateTime dt = new UFDateTime(System.currentTimeMillis() - 60 * 24 * 60 * 1000);
		String querySql = "select sm_msg_user.pk_message_user," + "       sm_msg_user.pk_user,"
				+ "       sm_msg_user.message_type," + "       bd_psndoc.def10 psndoccode,"
				+ "       sm_msg_notice.sendtime," + "       sm_msg_notice.subject" + "  from sm_msg_user"
				+ "  left join sm_msg_notice" + "    on sm_msg_user.pk_message = sm_msg_notice.pk_message"
				+ "  left join sm_user" + "    on sm_msg_user.pk_user = sm_user.cuserid" + "  left join bd_psndoc"
				+ "    on sm_user.pk_psndoc = bd_psndoc.pk_psndoc" + " where sm_msg_user.destination = 'inbox'"
				+ "   and sm_msg_user.message_type = 'notice'" + "   and isdelete = 'N'" + "   and isread = 'N'"
				+ "   and nvl(sm_msg_user.dr,0) = 0 and sm_msg_user.ts > '" + dt + "';";
		List<Map<String, String>> noticetList = (List<Map<String, String>>) getBaseDAO().executeQuery(querySql,
				new MapListProcessor());
		return noticetList;
	}

	/**
	 * 待办消息转为请求OA系统的VO数据
	 *
	 * @param workNoteVO
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private Map<String,String> unapproveBillsToOAToDO(WorkNoteVO workNoteVO) throws Exception {

		Map retmap = new HashMap<String,String>();
		BaseDAO baseDao = new BaseDAO();
		if (null != workNoteVO) {
			String querySql = "select ap.billid, ap.pk_message,ap.billno, ap.msgsourcetype,ap.subject, ap.sendtime, bt.billtypename, rp.appcode, rp.pagecode "
					+ "from sm_msg_approve ap, bd_billtype bt, bd_relatedapp rp " + "where ap.pk_detail = '"
					+ workNoteVO.getPk_checkflow()
					+ "' and bt.pk_billtypecode = ap.billtype and rp.pk_billtypecode = bt.pk_billtypecode "
					+ "and ( nvl ( rp.dr, 0 ) = 0 ) and rp.sence = 3 and rp.priority<10 ";

			Map<String, String> resultMap =  (Map<String, String>) getBaseDAO().executeQuery(querySql, new MapProcessor());
			if (null == resultMap || resultMap.size() == 0) {
				// 如果为空则需要重新根据交易类型查询
				querySql = "select ap.billid," + "       ap.billno," + "       ap.pk_message,"
						+ "       ap.msgsourcetype," + "       ap.subject," + "       ap.sendtime,"
						+ "       bt.billtypename," + "       regis.code appcode," + "       pub.pagecode, "
						+ "       bt.pk_billtypecode," + "       pub.sence"
						+ "  from sm_msg_approve ap,pub_workflownote wf, bd_billtype bt, pub_publishapp pub, sm_appregister regis"
						+ " where ap.pk_detail = '" + workNoteVO.getPk_checkflow() + "'"
						+ "   and ap.pk_detail = wf.pk_checkflow" + "   and bt.pk_billtypecode = ap.billtype"
						+ "   and pub.pk_billtypecode = bt.pk_billtypecode"
						+ "   and pub.publishappid = regis.pk_appregister"
						+ "   and (nvl(pub.dr, 0) = 0) and pub.sence = 3 " + "   order by pub.pagecode asc";

				resultMap = (Map<String, String>) getBaseDAO().executeQuery(querySql, new MapProcessor());
			}
			// 如果根据交易类型查询为空，则再根据父类编码自身查询一次
			if (null == resultMap || resultMap.size() == 0) {
				querySql = "select ap.billid,ap.billno, ap.pk_message, ap.msgsourcetype,ap.subject, ap.sendtime, bt.billtypename, rp.appcode, rp.pagecode "
						+ "from sm_msg_approve ap, bd_billtype bt, bd_relatedapp rp " + "where ap.pk_detail = '"
						+ workNoteVO.getPk_checkflow()
						+ "' and bt.pk_billtypecode = ap.billtype and rp.pk_billtypecode = bt.parentbilltype "
						+ "and ( nvl ( rp.dr, 0 ) = 0 ) and rp.sence = 3 and rp.priority<10";

				resultMap = (Map<String, String>) getBaseDAO().executeQuery(querySql, new MapProcessor());
			}

			if (null != resultMap && resultMap.size() > 0) {
				if(resultMap.get("oastatus")==null) {
					String sendJson = generateMsgAddSendData(workNoteVO, resultMap);
					retmap.put("oa", sendJson);
				}
			}
		}
		return retmap;
	}

	/**
	 * 拼接及赋值OA
	 * @param workNoteVO
	 * @param resultMap
	 * @return
	 * @throws Exception
	 */
	private String generateMsgAddSendData( WorkNoteVO workNoteVO,
			Map<String, String> resultMap) throws Exception {
		String pk_message = resultMap.get("pk_message");
		String appcode = resultMap.get("appcode");
		String pagecode = resultMap.get("pagecode");
		String pk_billtypecode = resultMap.get("pk_billtypecode");
		String subject = resultMap.get("subject");
		UserVO userVO = HttpUtil.getCodeByUserPk(workNoteVO.getCheckman());
		String pk_psndoc = userVO.getPk_psndoc();
		PsndocVO psndocVO = (PsndocVO)NCLocator.getInstance().lookup(IPersistenceRetrieve.class).retrieveByPk((String)null, PsndocVO.class, pk_psndoc);
		String billid = resultMap.get("billid");
		String billno = resultMap.get("billno");

		if(pagecode==null) {
			nc.bs.logging.Logger.error("单据类型【"+ pk_billtypecode + "】未关联应用编码，请在单据类型管理处设置！");
			throw new BusinessException("单据类型【"+ pk_billtypecode + "】未关联应用编码，请在单据类型管理处设置！");
		}
		// 参数列表
		StringBuilder desBuilder = new StringBuilder();
		desBuilder.append("pk_message=").append(pk_message).append("&pk_bill=").append(billid).append("&billno=").append(billno)
		.append("&c=").append(appcode).append("&p=").append(pagecode).append("&usercode=").append(userVO.getUser_code());

		Properties nccProperties= HttpUtil.getNccUrlInfo();
		//		外网没有端口
		String pcUrl = nccProperties.get("ip") +  BASE_APPROVE_URL + desBuilder.toString();
		//		手机端url
		String busicode = (String)nccProperties.get("busicentercode");
		StringBuilder mobBuilder = new StringBuilder();
		mobBuilder.append("&usercode=").append(userVO.getUser_code()).append("&busicode=").append(busicode).append("&billno=").append(billno);
		//		外网没有端口
		String mobUrl = nccProperties.get("ip") + BASE_APPROVE_URL_MOB + mobBuilder.toString();

		JSONObject jsobj = new JSONObject();
		//		title：标题
		jsobj.put("title", subject);
		//	    发布方应用编码（平台发放）
		jsobj.put("publisher", "ncc001");
		//	    sourceId：第三方待办id
		jsobj.put("sourceId", workNoteVO.getPk_checkflow());
		//	    createTime：待办产生时间
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		UFDateTime senddate = workNoteVO.getSenddate();
		//	    senddate.get
		long millis = senddate.getMillis();
		String format = simpleDateFormat.format(new Date(millis));
		jsobj.put("createTime", format);
		//	    userId: 办理人id（和email二选一）
		jsobj.put("userId", psndocVO.getCode());
		//	    email: 办理人邮箱（和id二选一）
		jsobj.put("email", userVO.getEmail());
		//	    url: 待办跳转url
		jsobj.put("url", pcUrl);
		//	      移动端url: 待办跳转url
		jsobj.put("mobUrl", mobUrl);
		return jsobj.toJSONString();
	}


	/**
	 * 待办消息转为已办请求OA系统的VO数据
	 *
	 * @param workNoteVO
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private String synApprovedBillsToOA(WorkNoteVO workNoteVO) throws Exception {
		String sendJson = null;
		BaseDAO baseDao = new BaseDAO();
		if (null != workNoteVO) {
			String querySql = "select ap.billid, ap.pk_message,ap.billno, ap.msgsourcetype,ap.subject, ap.sendtime, bt.billtypename, rp.appcode, rp.pagecode "
					+ "from sm_msg_approve ap, bd_billtype bt, bd_relatedapp rp " + "where ap.pk_detail = '"
					+ workNoteVO.getPk_checkflow()
					+ "' and bt.pk_billtypecode = ap.billtype and rp.pk_billtypecode = bt.pk_billtypecode "
					+ "and ( nvl ( rp.dr, 0 ) = 0 ) and rp.sence = 3 and rp.priority<10 ";

			Map<String, String> resultMap =  (Map<String, String>) getBaseDAO().executeQuery(querySql, new MapProcessor());
			if (null == resultMap || resultMap.size() == 0) {
				// 如果为空则需要重新根据交易类型查询
				querySql = "select ap.billid," + "       ap.billno," + "       ap.pk_message,"
						+ "       ap.msgsourcetype," + "       ap.subject," + "       ap.sendtime,"
						+ "       bt.billtypename," + "       regis.code appcode," + "       pub.pagecode, "
						+ "       bt.pk_billtypecode," + "       pub.sence"
						+ "  from sm_msg_approve ap,pub_workflownote wf, bd_billtype bt, pub_publishapp pub, sm_appregister regis"
						+ " where ap.pk_detail = '" + workNoteVO.getPk_checkflow() + "'"
						+ "   and ap.pk_detail = wf.pk_checkflow" + "   and bt.pk_billtypecode = ap.billtype"
						+ "   and pub.pk_billtypecode = bt.pk_billtypecode"
						+ "   and pub.publishappid = regis.pk_appregister"
						+ "   and (nvl(pub.dr, 0) = 0) and pub.sence = 3 " + "   order by pub.pagecode asc";

				resultMap = (Map<String, String>) getBaseDAO().executeQuery(querySql, new MapProcessor());
			}
			// 如果根据交易类型查询为空，则再根据父类编码自身查询一次
			if (null == resultMap || resultMap.size() == 0) {
				querySql = "select ap.billid,ap.billno, ap.pk_message, ap.msgsourcetype,ap.subject, ap.sendtime, bt.billtypename, rp.appcode, rp.pagecode "
						+ "from sm_msg_approve ap, bd_billtype bt, bd_relatedapp rp " + "where ap.pk_detail = '"
						+ workNoteVO.getPk_checkflow()
						+ "' and bt.pk_billtypecode = ap.billtype and rp.pk_billtypecode = bt.parentbilltype "
						+ "and ( nvl ( rp.dr, 0 ) = 0 ) and rp.sence = 3 and rp.priority<10";

				resultMap = (Map<String, String>) getBaseDAO().executeQuery(querySql, new MapProcessor());
			}
			if (null != resultMap && resultMap.size() > 0) {
				sendJson = this.generateMsgDoneSendData(workNoteVO, resultMap);
			}
		}
		return sendJson;
	}

	/**
	 * 拼接及赋值
	 *
	 * @param workNoteVO
	 * @param resultMap
	 * @throws Exception
	 */
	private String generateMsgDoneSendData(WorkNoteVO workNoteVO, Map<String, String> resultMap)
			throws Exception {
		Properties nccProperties= HttpUtil.getNccUrlInfo();
		String billno = resultMap.get("billno");
		//		手机端url
		String busicode = (String)nccProperties.get("busicentercode");
		StringBuilder mobBuilder = new StringBuilder();
		UserVO userVO = HttpUtil.getCodeByUserPk(workNoteVO.getCheckman());
		mobBuilder.append("&usercode=").append(userVO.getUser_code()).append("&busicode=").append(busicode).append("&billno=").append(billno);
		//		外网没有端口
		String mobUrl = nccProperties.get("ip") + BASE_APPROVE_URL_MOB + mobBuilder.toString();

		JSONObject jsobj = new JSONObject();
		//		publisher：发布方应用编码（平台发放）
		jsobj.put("publisher", "ncc001");
		//		sourceId：第三方待办id
		jsobj.put("sourceId", workNoteVO.getPk_checkflow());
		//	      移动端url: 待办跳转url
		jsobj.put("mobUrl", mobUrl);
		return jsobj.toJSONString();
	}

	/**
	 * 数据库查询出的数据转为请求的json数据
	 * @param map
	 * @return
	 * @throws Exception
	 */
	private String queryMapToContext(Map<String, String> map) throws Exception {
		String pk_message = map.get("pk_detail");
		String urlData = "";
		urlData = "ncc001/"+pk_message;
		return urlData;
	}

	/**
	 * 新增消息回写发送状态
	 * @param workNoteVO
	 * @throws BusinessException
	 */
	private void synNCCBills(WorkNoteVO workNoteVO) throws BusinessException {
		nc.bs.logging.Logger.error("PendingTrial ----- 执行数据库操作");
		String oaupdstr="~";
		String pk_checkflow = workNoteVO.getPk_checkflow();
		if (null != workNoteVO) {
			oaupdstr="已推送";
			String updatesql = "update pub_workflownote set userObject = '已推送'  where pk_checkflow = '" + pk_checkflow + "'";
			getBaseDAO().executeUpdate(updatesql);
		}
		//更新sm_msg_approve表，删除时用
		String updstr=oaupdstr;
		String updatesqlWXEx = "update sm_msg_approve set message_service = '" + updstr + "' where pk_detail = '" + pk_checkflow + "'";
		getBaseDAO().executeUpdate(updatesqlWXEx);
	}
	/**
	 * 待办变已办回写发送状态
	 * @param workNoteVO
	 * @throws BusinessException
	 */
	private void synNCCBillsDone(WorkNoteVO workNoteVO) throws BusinessException {
		String oaupdstr="~";
		String pk_checkflow = workNoteVO.getPk_checkflow();
		if (null != workNoteVO) {
			oaupdstr="已同步";
			String updatesql = "update pub_workflownote set userObject = '已同步'  where pk_checkflow = '" + pk_checkflow + "'";
			getBaseDAO().executeUpdate(updatesql);
		}
		//更新sm_msg_approve表，删除时用
		String updstr=oaupdstr;
		String updatesqlWXEx = "update sm_msg_approve set message_service = '" + updstr + "' where pk_detail = '" + pk_checkflow + "'";
		getBaseDAO().executeUpdate(updatesqlWXEx);
	}

	/**
	 *
	 * 删除OA那边的待办消息后，为避免下次继续发送，需要在sm_msg_user 删除
	 * @param map
	 * @throws BusinessException
	 */
	private void synNCCBills(Map<String, String> map) throws BusinessException {
		nc.bs.logging.Logger.error("WithdrawPending ----- 执行数据库操作");
		if (null != map) {
			if (null != map.get("pk_message_user")) {
				String pk_message_user = map.get("pk_message_user");
				String querySql = " update sm_msg_user set dr='1' where pk_message_user ='" + pk_message_user + "'";
				getBaseDAO().executeUpdate(querySql);
				nc.bs.logging.Logger.error("WithdrawPending ----- 开始执行更新NCC工作流状态标识");
			}
		}
	}

	public BaseDAO getBaseDAO() {
		if(null == baseDAO) {
			baseDAO = new BaseDAO(InvocationInfoProxy.getInstance().getUserDataSource());
		}
		return baseDAO;
	}
	
	
}
